using System;
using System.Collections.Generic;
using Less.Security.DAL.Records;

namespace Less.Security.DAL
{
	public static class RolesDAL
	{
        /// <summary>
        /// <add key="TableName" value="Roles"/>
        /// </summary>
        /// <returns></returns>
        public static string TableName
        {
            get { return Config.GetValue<string>("RolesDAL.TableName", "Roles"); }
            set { Config.SetValue("RolesDAL.TableName", value); }
        }

        /// <summary>
        /// <add key="IdColumnName" value="Id"/>
        /// </summary>
        /// <returns></returns>
        public static string IdColumnName
        {
            get { return Config.GetValue<string>("RolesDAL.IdColumnName", "Id"); }
            set { Config.SetValue("RolesDAL.IdColumnName", value); }
        }

        /// <summary>
        /// <add key="ExistsTable" value="SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Roles' "/>
        /// </summary>
        /// <returns></returns>
        public static string ExistsTable
        {
            get { return Config.GetValue<string>("RolesDAL.ExistsTable", "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Roles' "); }
            set { Config.SetValue("RolesDAL.ExistsTable", value); }
        }

        /// <summary>
        /// <add key="CreateTable" value="CREATE TABLE Roles (Id bigint IDENTITY(1,1) PRIMARY KEY,Name	nvarchar(100)	NOT NULL )"/>
        /// </summary>
        /// <returns></returns>
        public static string CreateTable
        {
            get
            {
                return Config.GetValue<string>("RolesDAL.CreateTable", @"CREATE TABLE Roles (Id bigint IDENTITY(1,1) PRIMARY KEY,Name	nvarchar(100)	NOT NULL )");
            }
            set { Config.SetValue("RolesDAL.CreateTable", value); }
        }

        /// <summary>
        /// <add key="SelectRolesByUserNameSQL" value="SELECT r.* FROM {0} r INNER JOIN UsersRoles ur ON r.Id = ur.RoleId INNER JOIN Users u ON u.Id = ur.UserId WHERE u.UserName = @0  "/>
        /// </summary>
        /// <returns></returns>
        public static string SelectRolesByUserNameSQL
        {
            get { return Config.GetValue<string>("RolesDAL.SelectRolesByUserNameSQL", @"SELECT r.* FROM {0} r INNER JOIN UsersRoles ur ON r.Id = ur.RoleId INNER JOIN Users u ON u.Id = ur.UserId WHERE u.UserName = @0"); }
            set { Config.SetValue("RolesDAL.SelectRolesByUserNameSQL", value); }
        }

        public static object token = new object();
        private static bool _tableReady = false;
        public static bool TableReady
        {
            get
            {
                lock (token)
                {
                    if (_tableReady == false)
                        _tableReady = PrepareTable();
                    return _tableReady;
                }
            }
            set { _tableReady = value; }
        }

        private static bool PrepareTable()
        {
            bool tableOk = false;
            try
            {
                var executeScalar = Data.Database.ExecuteScalar<object>(ExistsTable);
                if (executeScalar == null)
                {
                    Log.Warning(string.Format("Table {0} Does not exists", TableName), Log.LogEntry.EntryCategories.DataBase);
                    Data.Database.Execute(CreateTable);
                    Log.Info(string.Format("Table {0} Created", TableName), Log.LogEntry.EntryCategories.DataBase);
                }
                else
                {
                    Log.Debug(string.Format("Table {0} Already Exists", TableName), Log.LogEntry.EntryCategories.DataBase);
                }
                tableOk = true;
            }
            catch (Exception exception)
            {
                Log.Error(exception);
            }
            return tableOk;
        }

		/// <summary>
		/// Gets all the Role Records
		/// </summary>
		/// <returns></returns>
		public static List<RoleRecord> SelectAll()
		{
			List<RoleRecord> list = null;
			if (TableReady)
				list = Data.Database.Fetch<RoleRecord>(String.Format("SELECT * FROM {0}", TableName));
			return list;
		}

		/// <summary>
		/// Gets a Role Record 
		/// </summary>
		/// <param name="roleName">The Role name</param>
		/// <returns>The Role Record </returns>
		public static RoleRecord SelectByName(string roleName)
		{
		    RoleRecord record = null;
		    if (TableReady)
                record = Data.Database.SingleOrDefault<RoleRecord>(String.Format("SELECT * FROM {0} WHERE Name=@0", TableName), roleName);
			return record;
		}

		/// <summary>
		/// Gets all the Role Records related with a User of a given name
		/// </summary>
		/// <param name="userName">The name of the User</param>
		/// <returns></returns>
		public static List<RoleRecord> SelectRolesByUserName(string userName)
		{
			List<RoleRecord> list = null;
			if (TableReady && UsersDAL.TableReady && UsersRolesDAL.TableReady)
                list = Data.Database.Fetch<RoleRecord>(string.Format(SelectRolesByUserNameSQL, TableName), userName);
			return list;
		}

        /// <summary>
        /// Inserts role record from its name
        /// </summary>
        /// <param name="roleName"></param>
        /// <returns></returns>
		public static bool InsertRoleByName(string roleName)
		{
            if (TableReady)
            {
                RoleRecord role = SelectByName(roleName);
                if (role == null)
                {
                    role = new RoleRecord() { Name = roleName };
                    Data.Database.Insert(TableName, IdColumnName, role);
                    return true;
                }
                else
                {
                    Log.Warning(string.Format("Role {0} alredy exists", roleName));
                    return false;
                }
            }
            else return false;
		}
        /// <summary>
        /// Deletes a Role record from its name
        /// </summary>
        /// <param name="roleName"></param>
        /// <returns></returns>
		public static bool DeleteRoleByName(string roleName)
		{
            if (TableReady)
            {
                RoleRecord role = SelectByName(roleName);
                if (role == null)
                {
                    role = new RoleRecord() {Name = roleName};
                    return Data.Database.Delete(TableName, IdColumnName, role) > 0;
                }
                else
                {
                    Log.Warning(string.Format("Role {0} alredy exists", roleName));
                    return false;
                }
            }
            else
            {
                return false;
            }
		}
	}
}